from openpyxl import load_workbook
import xlrd
import xlwt
import os

filename2 = 'F:\python\XU22\TEST\XU1.0系统测试用例-模板 - 副本.xlsx'
filename = 'F:\python\XU22\TEST\XU1.0系统测试用例-模板.xlsx'
mergefile = 'F:\python\XU22\TEST\\allCase.xlsx'
def replace_xls(filename,sheetname,mergefile):
    wb = load_workbook(filename)
    # wb2 = load_workbook(filename2)
    wb3 =load_workbook(mergefile)

    ws = wb[sheetname]
    print(ws)
    # ws2 = wb2[sheetname]
    # print(ws2)
    ws3 = wb3[sheetname]

    # 两个for循环遍历整个excel的单元格内容
    for i,row in enumerate(ws.iter_rows()):
        for j,cell in enumerate(row):
            ws3.cell(row= i +1,column=j+1,value = cell.value)
    # for i,row in enumerate(ws2.iter_rows()):
    #     for j,cell in enumerate(row):
    #         ws3.cell(row= i +1,column=j+1,value = cell.value)

    wb3.save(mergefile)

# sheetnames= [['vmware概览_张银龙', '资源管理_张银龙', 'vmware云主机_赵月玲'],['vmware云硬盘&回收站_张银龙', 'vmware网络_张银龙']]
sheetnames= ['vmware云硬盘&回收站_张银龙', 'vmware网络_张银龙']

for sheetname in sheetnames:
    replace_xls(sheetname)

# 遇到复制几十个sheet时候，很有必要写个循环
# for i in range(len(sheetnames)):



# def getSheetNum(self,fname):
#     global SheetNumber
#     workbook = xlrd.open_workbook(fname)
#     all_SheetList = workbook.sheet_names()
#     SheetNumber = len(all_SheetList)
#     # print('sheetNumber::',SheetNumber)
#     return SheetNumber



